using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Grebok.Domain;
using Grebok.Persistence;
using Grebok.Persistence.QueryModel;

namespace Grebok.Persistence.AdoNet
{
	/// <summary>
	/// Summary description for CustomerDataAccessProvider.
	/// </summary>
	internal class CustomerDataMapper : IDataMapper<Customer>
	{
		private static readonly string insertCustomerCommandText = "INSERT INTO Customers (CustomerId, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerId, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)";
		private static readonly string updateCustomerByCommandText = "UPDATE Customers SET CompanyName=@CompanyName, ContactName=@ContactName, ContactTitle=@ContactTitle, Address=@Address, City=@City, Region=@Region, PostalCode=@PostalCode, Country=@Country, Phone=@Phone, Fax=@Fax WHERE CustomerId=@CustomerId";
		private static readonly string deleteCustomerByIdCommandText = "DELETE FROM Customers WHERE CustomerId=@CustomerId";

		#region ICustomerDataMapper Members

		public virtual void Create(Customer item)
		{
			SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString, 
				CommandType.Text, 
				insertCustomerCommandText,
				GetParametersByEntity(item));
		}

		public virtual void Delete(Customer item)
		{
			SqlParameter customerIdParameter = new SqlParameter("@CustomerId", SqlDbType.NChar, 5);
			customerIdParameter.Value = item.Id;
			SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString, 
				CommandType.Text, 
				deleteCustomerByIdCommandText,
				new SqlParameter[]{customerIdParameter});
		}

		public virtual void Update(Customer item)
		{
			SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString, 
				CommandType.Text, 
				updateCustomerByCommandText,
				GetParametersByEntity(item));
		}

		public virtual IList<Customer> GetAll()
		{
            List<Customer> customers = new List<Customer>();
			SqlDataReader customersReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString, 
										CommandType.Text, 
										"SELECT * FROM Customers");
			while(customersReader.Read())
			{
				customers.Add(BuildDataObjectFromRawData(customersReader));
			}
			customersReader.Close();
			return customers;
		}

		public virtual Customer GetByKey(object customerId)
		{
			Customer customer = new UnknownCustomer();
			SqlParameter customerIdParameter = new SqlParameter("@CustomerId", SqlDbType.NChar, 5);
			customerIdParameter.Value = customerId;
			SqlDataReader customersReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString, 
							CommandType.Text, 
							"SELECT * FROM Customers WHERE CustomerId=@CustomerId",
							new SqlParameter[]{customerIdParameter});
			if(customersReader.Read())
				customer = BuildDataObjectFromRawData(customersReader);
			customersReader.Close();
			return customer;
		}
        
        public IList<Customer> GetByCriteria(Query query)
        {
            throw new NotImplementedException();
        }

		#endregion

		protected virtual Customer BuildDataObjectFromRawData(IDataRecord rawData)
		{
			Customer customer = new Customer();
			customer.Id = (string) rawData["CustomerId"];
			customer.CompanyName = (string) rawData["CompanyName"];
            customer.ContactInfo.ContactName = rawData["ContactName"] is DBNull ? string.Empty : (string)rawData["ContactName"];
            customer.ContactInfo.ContactTitle = rawData["ContactTitle"] is DBNull ? string.Empty : (string)rawData["ContactTitle"];
			customer.AddressInfo.Address = rawData["Address"] is DBNull ? string.Empty : (string) rawData["Address"];
            customer.AddressInfo.City = rawData["City"] is DBNull ? string.Empty : (string)rawData["City"];
            customer.AddressInfo.Country = rawData["Country"] is DBNull ? string.Empty : (string)rawData["Country"];
			customer.FaxNumber = rawData["fax"] is DBNull ? string.Empty : (string) rawData["fax"];
			customer.PhoneNumber = rawData["phone"] is DBNull ? string.Empty : (string) rawData["phone"];
            customer.AddressInfo.PostalCode = rawData["PostalCode"] is DBNull ? string.Empty : (string)rawData["PostalCode"];
            customer.AddressInfo.Region = rawData["Region"] is DBNull ? string.Empty : (string)rawData["Region"];
			return customer;
		}


		protected virtual SqlParameter[] GetParametersByEntity(Customer item)
		{
			SqlParameter customerIdParameter = new SqlParameter("@CustomerId", SqlDbType.NChar, 5);
			customerIdParameter.Value = item.Id;
			SqlParameter companyNameParameter = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40);
			companyNameParameter.Value = item.CompanyName;
			SqlParameter contactNameParameter = new SqlParameter("@ContactName", SqlDbType.NVarChar, 30);
            contactNameParameter.Value = item.ContactInfo.ContactName;
			SqlParameter contactTitleParameter = new SqlParameter("@ContactTitle", SqlDbType.NVarChar, 30);
            contactTitleParameter.Value = item.ContactInfo.ContactTitle;
			SqlParameter addressParameter = new SqlParameter("@Address", SqlDbType.NVarChar, 60);
            addressParameter.Value = item.AddressInfo.Address;
			SqlParameter cityParameter = new SqlParameter("@City", SqlDbType.NVarChar, 15);
            cityParameter.Value = item.AddressInfo.City;
			SqlParameter regionParameter = new SqlParameter("@Region", SqlDbType.NVarChar, 15);
            regionParameter.Value = item.AddressInfo.Region;
			SqlParameter postalCodeParameter = new SqlParameter("@PostalCode", SqlDbType.NVarChar, 10);
            postalCodeParameter.Value = item.AddressInfo.PostalCode;
			SqlParameter countryParameter = new SqlParameter("@Country", SqlDbType.NVarChar, 15);
            countryParameter.Value = item.AddressInfo.Country;
			SqlParameter phoneParameter = new SqlParameter("@Phone", SqlDbType.NVarChar, 24);
			phoneParameter.Value = item.PhoneNumber;
			SqlParameter faxParameter = new SqlParameter("@Fax", SqlDbType.NVarChar, 24);
			faxParameter.Value = item.FaxNumber;
		
			return new SqlParameter[]{
										 customerIdParameter, 
										 companyNameParameter,
										 contactNameParameter,
										 contactTitleParameter,
										 addressParameter, 
										 cityParameter, 
										 regionParameter, 
										 postalCodeParameter, 
										 countryParameter, 
										 phoneParameter, 
										 faxParameter
									 };
		}
	}
}
